﻿CREATE PROCEDURE [dbo].[books_get_profile_rated_books_by_login_or_email]
    @page int = 0,
    @size int = 0,
    @column nvarchar(256),
    @direction int = 0,
    @email nvarchar(256) = null,
    @currentUserId int = 0

AS
DECLARE @userId int = 0

IF(@email IS NOT NULL)
    SELECT @userId = Users.UserId
    FROM Users
    WHERE Users.[Login] = @email or Users.Email = @email

SELECT
    Books.BookId,
    Books.Title AS Book_Title,
    Books.Avatar AS Book_Avatar,
    Books.ShortContent,
    Books.[FileName],
    Rating = cast((SELECT Sum(Value) / Count(1)   From Ratings Where (Ratings.BookId = Books.BookId)) as nvarchar),
    IsRated = cast((SELECT Count(1) From Ratings Where (Ratings.BookId=Books.BookId AND Ratings.UserId=@currentUserId)) as nvarchar)
FROM Books
inner join Ratings on Ratings.UserId = @userId  
WHERE Books.BookId = Ratings.BookId
ORDER BY
    CASE WHEN @column = 'Name' AND @direction = 1 THEN Books.Title END DESC,
    CASE WHEN @column = 'Name' AND @direction = 0 THEN Books.Title END ASC,
    CASE WHEN @column = 'DateOfPublish' AND @direction = 1 THEN Books.DateOfPublish END DESC,
    CASE WHEN @column = 'DateOfPublish' AND @direction = 0 THEN Books.DateOfPublish END ASC

OFFSET ((@page-1)*@size) ROWS
FETCH NEXT @size ROWS ONLY; 

RETURN 0
